/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.PXuatKho;
import POJO.SoLuongXuat;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Khoi
 */
public class PXuatKhoDao {

	public static ArrayList<PXuatKho> layDanhSach() {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		String sql = "SELECT  MaPXK, NgayXuat, MaNVPhuTrach, MaHD   FROM  PXuatKho ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				PXuatKho tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int laySoDong(int MaNVPhuTrach) {
		String sql = "select count(MaPXK) as COU from PXuatKho where MaNVPhuTrach =" + MaNVPhuTrach;
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COU");
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static ArrayList<SoLuongXuat> layDanhSachSoLuongDaXuat(PXuatKho px) {
		ArrayList<SoLuongXuat> ds = new ArrayList<SoLuongXuat>();
		String str = "select px.MaHD,ct.MaSP, sum(ct.SoLuong)as SoLuong from PXuatKho px, CTPXuatKho ct "
			 + "where ct.MaPXK = px.MaPXK and px.MaHD = " + px.getMaHD()
			 + " group by px.MaHD,ct.MaSP";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int mahd = rs.getInt("MaHD");
				int masp = rs.getInt("MaSP");
				int sl = rs.getInt("SoLuong");
				SoLuongXuat tmp = new SoLuongXuat(mahd, masp, sl);
				ds.add(tmp);
			}

		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int laySoDong() {
		String sql = "select count(MaPXK) as COU from PXuatKho ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COU");
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static ArrayList<PXuatKho> layDanhSachByNhanVien(int maNV) {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		String sql = "SELECT  MaPXK, NgayXuat, MaNVPhuTrach, MaHD   FROM  PXuatKho where MaNVPhuTrach = " + maNV;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				PXuatKho tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<PXuatKho> layDanhSachByNhanVienPhanTrang(int MaNV, int soDong, int trangSo) {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		String sql = "with s as ( select ROW_NUMBER() OVER(ORDER By MaPXK desc) as row ,PXuatKho.* from PXuatKho where PXuatKho.MaNVPhuTrach =" + MaNV
			 + " ) select * from s where row between " + (soDong * trangSo + 1)
			 + " and " + (trangSo * soDong + soDong)
			 + " order by MaPXK asc;";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				PXuatKho tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
        
        public static ArrayList<PXuatKho> layDanhSachPhanTrang(int soDong, int trangSo) {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		String sql = "with s as ( select ROW_NUMBER() OVER(ORDER By MaPXK desc) as row ,PXuatKho.* from PXuatKho "
			 + " ) select * from s where row between " + (soDong * trangSo + 1)
			 + " and " + (trangSo * soDong + soDong)
			 + " order by MaPXK asc;";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				PXuatKho tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<PXuatKho> timKiemByNgay(Date ngay) {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();

		String sql = "SELECT * FROM PXuatKho WHERE Day(NgayXuat) LIKE '%" + ngay + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				PXuatKho tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int layMaPXKMax() {
		String sql = "select ct.MaPXK from PXuatKho ct where ct.MaPXK >= all(select ct1.MaPXK from PXuatKho ct1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("MaPXK");
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean them(PXuatKho px) {
		boolean kq = false;
		int mpx = PXuatKhoDao.layMaPXKMax();
		mpx += 1;
		px.setMaPXK(mpx);
		String ngay = px.getNgayXuat().getYear() + "-" + ((Integer) px.getNgayXuat().getMonth()+1)  + "-" + px.getNgayXuat().getDate();
		String sql = "INSERT INTO  PXuatKho  ( MaPXK, NgayXuat, MaNVPhuTrach, MaHD ) VALUES ";

		String s = String.format("( %d, '%s', %d, %d)",
			 px.getMaPXK(),
			 ngay.toString(),
			 px.getMaNVPhuTrach(),
			 px.getMaHD());
		sql += s;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean xoa(int maPX) {
		boolean kq = false;
		String sql = String.format("DELETE FROM PXuatKho WHERE MaPXK = %d", maPX);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		return kq;
	}

	public static boolean capNhat(PXuatKho tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE PXuatKho"
			 + "SET"
			 + "NgayXuat = %s, "
			 + "MaNVPhuTrach = %d,"
			 + "MaHD = %d"
			 + "WHERE  MaPXK = %d; ", tmp.getNgayXuat(), tmp.getMaNVPhuTrach(), tmp.getMaHD(), tmp.getMaPXK());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static PXuatKho layByMa(int maPX) {
		PXuatKho tmp = null;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM PXuatKho WHERE MaPXK =" + maPX;
			cn = new KetNoi();
			rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
			}
		} catch (SQLException ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		return tmp;
	}

	public static ArrayList<PXuatKho> layByMaHD(int maHD) {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		PXuatKho tmp = null;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM PXuatKho WHERE MaHD =" + maHD;
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				tmp = new PXuatKho(ma, ngayxuat, manv, mahd);
				ds.add(tmp);
			}
		} catch (SQLException ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		return ds;
	}

//	public static int layMaMax() {
//		int kq = 0;
//		String sql = "Select Max(mapxk) as max from PXuatKho";
//		KetNoi cn = null;
//		ResultSet rs = null;
//		try {
//			cn = new KetNoi();
//			rs = cn.docBang(sql);
//			while (rs.next()) {
//				int ma = rs.getInt("max");
//				//return ma;
//				kq = ma;
//			}
//		} catch (Exception ex) {
//			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
//
//		} finally {
//			cn.dongKetNoi();
//		}
//
//		return kq;
//	}
	public static ArrayList<PXuatKho> layMaMax() {
		ArrayList<PXuatKho> ds = new ArrayList<PXuatKho>();
		PXuatKho tmp = null;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT PXK.*,MAX(MAPXK) as MaMax FROM PXuatKho as PXK Group by PXK.MaPXK,PXK.NgayXuat,PXK.MaNVPhuTrach,PXK.maHD";
			cn = new KetNoi();
			rs = cn.docBang(sql);
			if (rs.next()) {
				int maMAX = rs.getInt("MaMax");
				int ma = rs.getInt("MaPXK");
				Date ngayxuat = rs.getDate("NgayXuat");
				int manv = rs.getInt("MaNVPhuTrach");
				int mahd = rs.getInt("MaHD");
				tmp = new PXuatKho(ma, ngayxuat, manv, mahd, maMAX);
				ds.add(tmp);
			}
		} catch (SQLException ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		return ds;
	}

	public static int layMaMax2() {
		int kq = 0;
		String sql = "Select Max(MaPXK) as MaMax2 from PXuatKho";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaMax2");
				//return ma;
				kq = ma;
			}
		} catch (Exception ex) {
			Logger.getLogger(PXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}

		return kq;
	}
}
